{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "###########调包\n",
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from datetime import *\n",
    "import time\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "############数据文件文件路径\n",
    "train_dir = '../../contest/train/'\n",
    "test_dir = '../../contest/A榜/'\n",
    "train_pickle_dir = './pickle/train/'\n",
    "test_pickle_dir = './pickle/A/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "def count_notzero(series_x):\n",
    "    mode = series_x[(series_x > 0)]\n",
    "    return mode.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工法人流水1():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(test_dir,test_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            法人流水_T0 = pd.read_csv(os.path.join(train_dir,'XW_RPSTV_TR_DTAL.csv'))    \n",
    "            法人流水_T0.columns = ['法定代表人客户ID','交易日期','交易金额']\n",
    "        else:\n",
    "            法人流水_T0 = pd.read_csv(os.path.join(test_dir,'XW_RPSTV_TR_DTAL_A.csv'))    \n",
    "            法人流水_T0.columns = ['法定代表人客户ID','交易日期','交易金额']\n",
    "\n",
    "        法人流水_T0['交易金额'] = pow((法人流水_T0['交易金额'])/3.12,3).round(2)\n",
    "\n",
    "        法人流水_T0['交易日期'] = 法人流水_T0['交易日期'].astype('str')\n",
    "        法人流水_T0['交易日期'] = 法人流水_T0['交易日期'].astype('datetime64[ns]')\n",
    "        法人流水_T0['交易日期']=pd.to_datetime(法人流水_T0['交易日期'])+pd.DateOffset(days=11886)\n",
    "        法人流水_T0.sort_values(['法定代表人客户ID','交易日期'],inplace=True,ascending=True)\n",
    "        法人流水_T0['交易日期'] = 法人流水_T0['交易日期'].astype('str')\n",
    "        \n",
    "        法人流水_T0['流入金额'] =np.where((法人流水_T0['交易金额']>=0),法人流水_T0['交易金额'],0)\n",
    "        法人流水_T0['流出金额'] =np.where((法人流水_T0['交易金额']<=0),法人流水_T0['交易金额']*-1,0)\n",
    "\n",
    "        法人流水_单日汇总_T1=法人流水_T0.groupby(['法定代表人客户ID','交易日期']).agg({\\\n",
    "\t\t'流入金额':['sum',count_notzero],'流出金额':['sum',count_notzero]})\n",
    "        法人流水_单日汇总_T1.reset_index(inplace=True)\n",
    "        法人流水_单日汇总_T1.columns = ['法定代表人客户ID','交易日期','流入金额','流入笔数','流出金额','流出笔数']\n",
    "\n",
    "        法人流水_单日汇总_T1['最大日期']='2021-03-31'\n",
    "        法人流水_单日汇总_T1['最大日期'] = 法人流水_单日汇总_T1['最大日期'].astype('datetime64[ns]')\n",
    "        法人流水_单日汇总_T1['交易日期'] = 法人流水_单日汇总_T1['交易日期'].astype('datetime64[ns]')\n",
    "        法人流水_单日汇总_T1['交易离最大日期天']= 法人流水_单日汇总_T1.apply(lambda x:(x['最大日期']-x['交易日期']).days, axis=1)\n",
    "        法人流水_单日汇总_T1.drop(['最大日期'],axis=1,inplace=True)\n",
    "\n",
    "        pickle.dump(法人流水_单日汇总_T1, open(pickle_dir+'法人流水_临时表.p', 'wb'))\n",
    "        res.append(法人流水_单日汇总_T1)\n",
    "    return "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工法人流水2():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(test_dir,test_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            法人流水_T0 = pickle.load(open(train_pickle_dir+'法人流水_临时表.p', 'rb'))\n",
    "        else:\n",
    "            法人流水_T0 = pickle.load(open(test_pickle_dir+'法人流水_临时表.p', 'rb'))\n",
    "\n",
    "        法人流水_T0['交易月']=pd.DatetimeIndex(法人流水_T0['交易日期']).month\n",
    "\n",
    "        法人流水_按月汇总_T1=法人流水_T0.groupby(['法定代表人客户ID','交易月']).agg({\\\n",
    "\t\t'流入金额':['sum'],'流入笔数':['sum'],'流出金额':['sum'],'流出笔数':['sum'],'交易日期':['count']})\n",
    "        法人流水_按月汇总_T1.reset_index(inplace=True)\n",
    "        法人流水_按月汇总_T1.columns = ['法定代表人客户ID','交易月','月流入金额','月流入笔数','月流出金额','月流出笔数','月交易天数']\n",
    "\n",
    "        法人流水_汇总_T1=法人流水_按月汇总_T1.groupby(['法定代表人客户ID']).agg({\\\n",
    "\t\t'月流入金额':['sum'],'月流入笔数':['sum'],'月流出金额':['sum'],'月流出笔数':['sum'],'月交易天数':['sum']})\n",
    "        法人流水_汇总_T1.reset_index(inplace=True)\n",
    "        法人流水_汇总_T1.columns = ['法定代表人客户ID','法人总流入金额','法人总流入笔数','法人总流出金额','法人总流出笔数','法人总交易天数']\n",
    "\n",
    "        法人流水_汇总_T1['法人总净流']=法人流水_汇总_T1['法人总流入金额']-法人流水_汇总_T1['法人总流出金额']\n",
    "        法人流水_汇总_T1['法人总金额']=法人流水_汇总_T1['法人总流入金额']+法人流水_汇总_T1['法人总流出金额']\n",
    "\n",
    "        三月汇总_T0= 法人流水_按月汇总_T1.loc[法人流水_按月汇总_T1['交易月'] == 3]\n",
    "        三月汇总_T0.drop(['交易月'],axis=1,inplace=True)\n",
    "        三月汇总_T0.columns = ['法定代表人客户ID','法人3月流入金额','法人3月流入笔数','法人3月流出金额','法人3月流出笔数','法人3月交易天数']\n",
    "        三月汇总_T0['法人3月净流']=三月汇总_T0['法人3月流入金额']-三月汇总_T0['法人3月流出金额']\n",
    "        三月汇总_T0['法人3月总金额']=三月汇总_T0['法人3月流入金额']+三月汇总_T0['法人3月流出金额']\n",
    "\n",
    "        if data_dir==train_dir:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(train_dir,'XW_TARGET.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID','违约标记']\n",
    "            目标客户列表.drop(['违约标记'],axis=1,inplace=True)\n",
    "        else:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(test_dir,'XW_TARGET_A.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID']\n",
    "\n",
    "        法人流水特征=目标客户列表.merge(法人流水_汇总_T1,on=['法定代表人客户ID'],how='left')\n",
    "        法人流水特征=法人流水特征.merge(三月汇总_T0,on=['法定代表人客户ID'],how='left')\n",
    "        法人流水特征.drop(['借款合同编号','纳税人识别号','法定代表人客户ID'],axis=1,inplace=True)\n",
    "\n",
    "        pickle.dump(法人流水特征, open(pickle_dir+'法人流水特征.p', 'wb'))\n",
    "        res.append(法人流水特征)\n",
    "    return res[0],res[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工法人流水_补充():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(test_dir,test_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            法人流水_T1 = pickle.load(open(train_pickle_dir+'法人流水_临时表.p', 'rb'))\n",
    "        else:\n",
    "            法人流水_T1 = pickle.load(open(test_pickle_dir+'法人流水_临时表.p', 'rb'))\n",
    "\t\t\t\n",
    "        法人流水_T2=法人流水_T1.groupby(['法定代表人客户ID']).agg({'交易日期':['max']})\n",
    "        法人流水_T2.reset_index(inplace=True)\n",
    "        法人流水_T2.columns = ['法定代表人客户ID','交易日期']\n",
    "        \n",
    "        法人流水_T3=法人流水_T1.merge(法人流水_T2,on=['法定代表人客户ID','交易日期'],how='inner')\n",
    "        法人流水_T3['最后一天总净流']=法人流水_T3['流入金额']-法人流水_T3['流出金额']\n",
    "        法人流水_T3['最后一天总金额']=法人流水_T3['流入金额']+法人流水_T3['流出金额']\n",
    "\n",
    "        法人流水_T3.drop(['交易日期','流入金额','流出金额'],axis=1,inplace=True)\n",
    "\n",
    "        法人流水_T3.columns = ['法定代表人客户ID','法人最后一天流入笔数','法人最后一天流出笔数','法人最后一天日期差','法人最后一天总净流','法人最后一天总金额']\n",
    "\n",
    "        if data_dir==train_dir:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(train_dir,'XW_TARGET.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID','违约标记']\n",
    "            目标客户列表.drop(['违约标记'],axis=1,inplace=True)\n",
    "        else:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(test_dir,'XW_TARGET_A.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID']\n",
    "\n",
    "        法人流水补充=目标客户列表.merge(法人流水_T3,on=['法定代表人客户ID'],how='left')\n",
    "        法人流水补充.drop(['借款合同编号','纳税人识别号','法定代表人客户ID'],axis=1,inplace=True)\n",
    "\n",
    "        pickle.dump(法人流水补充, open(pickle_dir+'法人流水补充.p', 'wb'))\n",
    "        res.append(法人流水补充)\n",
    "    return res[0],res[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "加工法人流水1()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-21-08a282109940>:25: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  三月汇总_T0.drop(['交易月'],axis=1,inplace=True)\n",
      "<ipython-input-21-08a282109940>:27: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  三月汇总_T0['法人3月净流']=三月汇总_T0['法人3月流入金额']-三月汇总_T0['法人3月流出金额']\n",
      "<ipython-input-21-08a282109940>:28: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  三月汇总_T0['法人3月总金额']=三月汇总_T0['法人3月流入金额']+三月汇总_T0['法人3月流出金额']\n",
      "<ipython-input-21-08a282109940>:25: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  三月汇总_T0.drop(['交易月'],axis=1,inplace=True)\n",
      "<ipython-input-21-08a282109940>:27: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  三月汇总_T0['法人3月净流']=三月汇总_T0['法人3月流入金额']-三月汇总_T0['法人3月流出金额']\n",
      "<ipython-input-21-08a282109940>:28: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  三月汇总_T0['法人3月总金额']=三月汇总_T0['法人3月流入金额']+三月汇总_T0['法人3月流出金额']\n"
     ]
    }
   ],
   "source": [
    "法人流水训练集,法人流水测试集=加工法人流水2()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "法人流水补充训练集,法人流水补充测试集=加工法人流水_补充()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工法人流水_补充2():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(test_dir,test_pickle_dir)]:\t\t\n",
    "        if data_dir==train_dir:\n",
    "            法定代表人交易信息表 = pd.read_csv(os.path.join(data_dir,'XW_RPSTV_TR_DTAL.csv'))    \n",
    "        else:\n",
    "            法定代表人交易信息表 = pd.read_csv(os.path.join(data_dir,'XW_RPSTV_TR_DTAL_A.csv'))       \n",
    "        法定代表人交易信息表.columns = ['法定代表人客户ID','交易日期','交易金额']\n",
    "\n",
    "        法定代表人交易信息表['交易金额'] = pow((法定代表人交易信息表['交易金额'])/3.12,3).round(2)\n",
    "        法定代表人交易信息表['交易日期'] = 法定代表人交易信息表['交易日期'].astype('str')\n",
    "        法定代表人交易信息表['交易日期'] = 法定代表人交易信息表['交易日期'].astype('datetime64[ns]')\n",
    "        法定代表人交易信息表['交易日期']=pd.to_datetime(法定代表人交易信息表['交易日期'])+pd.DateOffset(days=11886)\n",
    "\n",
    "        #新增绝对值相关std\n",
    "        法人交易T0 = 法定代表人交易信息表.copy()\n",
    "        法人交易T0['法人金额绝对值'] =np.where((法定代表人交易信息表['交易金额']>=0),法定代表人交易信息表['交易金额'],法定代表人交易信息表['交易金额']*-1)\n",
    "        法人交易T0 = 法人交易T0.groupby(['法定代表人客户ID']).agg({'法人金额绝对值':['max','min','mean','std']})\n",
    "        法人交易T0.reset_index(inplace = True)\n",
    "        法人交易T0.columns=['法定代表人客户ID','法人交易绝对值最高金额','法人交易绝对值最低金额','法人交易绝对值_mean','法人交易绝对值_std']\n",
    "        \n",
    "        法人交易T1 = 法定代表人交易信息表.groupby(['法定代表人客户ID']).agg({'交易金额':['sum','max','min','count','mean','std']})\n",
    "        法人交易T1.reset_index(inplace = True)\n",
    "        法人交易T1.columns=['法定代表人客户ID','法人交易金额汇总','法人交易最高金额','法人交易最低金额','法人交易笔数','法人交易_mean','法人交易_std']\n",
    "        \n",
    "        法人交易T2 = 法定代表人交易信息表.drop(labels = ['交易金额'],axis=1)\n",
    "        法人交易T2 = 法人交易T2.drop_duplicates()\n",
    "        print(法人交易T2.shape)\n",
    "        法人交易T3 = 法人交易T2.groupby(['法定代表人客户ID']).agg({'交易日期':['count','max','min']})\n",
    "        法人交易T3.reset_index(inplace = True)\n",
    "        法人交易T3.columns=['法定代表人客户ID','法人交易天数','法人交易日期max','法人交易日期min']\n",
    "        法人交易T3['法人时间跨度']= 法人交易T3.apply(lambda x:(x['法人交易日期max']-x['法人交易日期min']).days, axis=1)\n",
    "        \n",
    "        法人交易T4=法定代表人交易信息表.loc[法定代表人交易信息表['交易金额']>=0]\n",
    "        法人交易T4=法人交易T4.groupby(['法定代表人客户ID']).agg({'交易金额':['sum','count','max','min','mean','std']})\n",
    "        法人交易T4.reset_index(inplace=True)\n",
    "        法人交易T4.columns=['法定代表人客户ID','法人流入总额','法人流入笔数','法人流入max','法人流入min','法人流入mean','法人流入std']\n",
    "        \n",
    "        法人交易T5=法定代表人交易信息表.loc[法定代表人交易信息表['交易金额']<0]\n",
    "        法人交易T5=法人交易T5.groupby(['法定代表人客户ID']).agg({'交易金额':['sum','count','max','min','mean','std']})\n",
    "        法人交易T5.reset_index(inplace=True)\n",
    "        法人交易T5.columns=['法定代表人客户ID','法人流出总额','法人流出笔数','法人流出max','法人流出min','法人流出mean','法人流出std']\n",
    "        \n",
    "        法人交易T6 = 法人交易T1.merge(法人交易T3,on='法定代表人客户ID',how='left')\n",
    "        法人交易T7 = 法人交易T6.merge(法人交易T4,on='法定代表人客户ID',how='left')\n",
    "        法人交易T8 = 法人交易T7.merge(法人交易T5,on='法定代表人客户ID',how='left')\n",
    "    \n",
    "        if data_dir==train_dir:\n",
    "            目标客户表 = pd.read_csv(os.path.join(data_dir,'XW_TARGET.csv'))\n",
    "            目标客户表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID','违约标记']\n",
    "            目标客户表.drop(['违约标记'],axis=1,inplace=True)\n",
    "        else:\n",
    "            目标客户表 = pd.read_csv(os.path.join(data_dir,'XW_TARGET_A.csv'))\n",
    "            目标客户表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID']\n",
    "\t\n",
    "        法人交易宽表 = 目标客户表.merge(法人交易T8,on='法定代表人客户ID',how='left')\n",
    "        法人交易宽表 = 法人交易宽表.drop(labels = ['纳税人识别号','法定代表人客户ID','借款合同编号','法人交易日期max','法人交易日期min'],axis=1)\n",
    "        for a in list(法人交易宽表.select_dtypes(include=['float']).columns.values):\n",
    "            法人交易宽表[a]=法人交易宽表[a].round(2)\n",
    "        pickle.dump(法人交易宽表, open(pickle_dir+'法人交易宽表DJY.p', 'wb'))\n",
    "        res.append(法人交易宽表)\n",
    "    return res[0],res[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1320301, 2)\n",
      "(134511, 2)\n"
     ]
    }
   ],
   "source": [
    "法人流水补充2训练集,法人流水补充2测试集 = 加工法人流水_补充2()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(50000, 21)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "法人流水补充2训练集.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5061, 21)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "法人流水补充2测试集.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  },
  "toc": {
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
